import redis
from fastapi import FastAPI, WebSocket
import duckdb

app = FastAPI()
r = redis.Redis(host='127.0.0.1', port=6379, db=0, password='123456', decode_responses=True, charset='utf-8', encoding='utf-8')

con = duckdb.connect(database='d:\charts\my4d.duckdb', read_only=True)

with open('bymonth.sql', 'r', encoding='utf-8') as f:
    sql = f.read()
    con.execute(sql)

with open('byzsxm.sql', 'r', encoding='utf-8') as f:
    sql = f.read()
    con.execute(sql)

with open('bysz.sql', 'r', encoding='utf-8') as f:
    sql = f.read()
    con.execute(sql)

def pretty_float(obj):
    if isinstance(obj, float):
        return round(obj, 2)
    elif isinstance(obj, dict):
        return {k: pretty_float(v) for k, v in obj.items()}
    elif isinstance(obj, (list, tuple)):
        return map(pretty_float, obj)
    
    return obj
def db_taxbymonth(year: int = 2024, dw: str = '143'):
    key = f"taxbymonth" + str(year) + dw

    if r.exists(key):
        res = r.get(key)
    else:
        sql = f"execute gettaxbymonth(nd:= {year}, jedw:= 10000, dw:= '{dw}%')".format(year, dw)
        #print(sql)
        df = con.execute(sql).df()
        #print(df)
        res = df.to_json(orient='values')
        print(res)
        res = pretty_float(res)
        print(res)
        r.set(key, res)
    return res

def db_taxbysz(year: int=2024, month: int=2, dw: str='143'):
    key = f"taxbysz" + str(year) + str(month) + dw

    if r.exists(key):
        res = r.get(key)
    else:
        sql = f"execute gettaxbysz(year:={year}, month:={month}, dw:= '{dw}%', jedw:=10000)".format(year, month, dw)
        print(sql)
        df = con.execute(sql).df()
        print(df)
        res = df.to_json(orient='values')
        print(res)
        res = pretty_float(res)
        r.set(key, res)
    return res

def db_taxbyzsxm(year: int=2024, month: int=4, dw: str='143'):
    key = f"taxbyzsxm" + str(year)+ str(month) + dw

    if r.exists(key):
        res = r.get(key)
    else:
        sql = f"execute gettaxbyzsxm(year:= {year}, month:= {month}, dw:= '{dw}%', jedw:=10000)".format(year, month, dw)
        print(sql)
        res = con.execute(sql).df().to_json(orient='values')
        print(res)
        r.set(key, res)
    return res


@app.get("/")
async def read_root():
    return {"功能": "API服务器", "版本": "1.0.0", "更新日期": "2024-05-20" }

@app.websocket("/ws")
async def websocket_endpoint(websocket: WebSocket):
    await websocket.accept()
    while True:
        data = await websocket.receive_text()
        await websocket.send_text(f"Msg was: {data}")

@app.get("/gettaxbymonth/{year}/{dw}")
async def get_taxbymonth(year: int, dw: str):
    return db_taxbymonth(year, dw)

@app.get("/gettaxbyzsxm/{year}/{month}/{dw}")
async def get_taxbyzsxm(year: int, month: int, dw: str):
    return db_taxbyzsxm(year, month, dw)

@app.get("/gettaxbysz/{year}/{month}/{dw}")
async def get_taxbysz(year:str, month: str, dw: str):
    return db_taxbysz(year, month, dw)